package gew.metadata;

import gew.services.LocaleService;
import gew.system.LabelCode;
import gew.sql.SqlTemplates;
import gew.utils.SqlUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.ResourceBundle;
import javax.servlet.http.Cookie;

public abstract class CustomSql {

    private static final String BUNDLE_NAME = "labels";
    private static final String FIELD_LEVEL_ALIAS = "level";
    private static final String FIELD_ID = "id";
    private static final String FIELD_NAME = "name";
    private static final String FIELD_CODE = "code";
    private static final String FIELD_CHILDREN = "children";
    private static final String FIELD_TOTAL = "total";
    private static final String FIELD_COLSPAN = "colspan";
    private static final String FIELD_FIELD_ID = "field_id";
    private static final String FIELD_LEVEL = "level";
    private static final String ALIAS_FCT = "f";
    private static final String ALIAS_ROW = "r";
    private static final String ALIAS_COL = "c";
    private static final String ALIAS_GRP = "g";
    private static final String ALIAS_MES = "m";
    private static final String ALIAS_LVL = "lvl";
    private static final String ALIAS_CHL = "chl";
    private static final int FCT_TRADE_ROW_SQL_INDEX = 0;
    private static final int FCT_TRADE_ROW_NAME_INDEX = 1;

    private static String getCellValue(Object[] row, int colIndex, String ifNullValue) {
        if (row.length > colIndex) {
            return row[colIndex] == null ? ifNullValue : row[colIndex].toString();
        } else {
            return ifNullValue;
        }
    }

    // <editor-fold defaultstate="collapsed" desc="TRADE DATA">
    public static String getTradeSummaryTable(String reporterIds, Integer partnerId, Integer commodityId, String baseUrl, String tradeFlowExportUrlCode, String tradeFlowImportUrlCode, String partnerUrlCode, String commodityClassificationUrlCode, String commodityUrlCode, String locale) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.TRADE_SUMMARY);
        result = result.replace("@ids", reporterIds);
        result = result.replace("@partnerId", partnerId.toString());
        result = result.replace("@commodityId", commodityId.toString());
        result = result.replace("@baseUrl", baseUrl);
        result = result.replace("@tradeFlowExportUrlCode", tradeFlowExportUrlCode);
        result = result.replace("@tradeFlowImportUrlCode", tradeFlowImportUrlCode);
        result = result.replace("@partnerUrlCode", partnerUrlCode);
        result = result.replace("@commodityClassificationUrlCode", commodityClassificationUrlCode);
        result = result.replace("@commodityUrlCode", commodityUrlCode);
        result = result.replace("@p_locale@", locale);
        result = result.replace("@name", ResourceBundle.getBundle(BUNDLE_NAME, LocaleService.getLocale(locale)).getString(LabelCode.SQL_HIER_NAME));
        return result;
    }

    public static String getTradeSummaryDefaultCommodity(String typeId) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.TRADE_SUMMARY_DEFAULT_COMMODITY);
        result = result.replace("@typeId@", typeId);
        return result;
    }

    public static String getMenuTrade(Locale locale) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.MENU_TRADE);
        result = result.replace("@name", ResourceBundle.getBundle(BUNDLE_NAME, locale).getString(LabelCode.SQL_HIER_NAME));
        return result;
    }

    public static String getMenuIndicators(Locale locale) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.MENU_INDICATORS);
        result = result.replace("@name", ResourceBundle.getBundle(BUNDLE_NAME, locale).getString(LabelCode.SQL_HIER_NAME));
        return result;
    }

    public static String getMenuIndustries(Locale locale) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.MENU_INDUSTRIES);
        result = result.replace("@name", ResourceBundle.getBundle(BUNDLE_NAME, locale).getString(LabelCode.SQL_HIER_NAME));
        return result;
    }

    // </editor-fold>
    // <editor-fold defaultstate="collapsed" desc="COMTRADE DATA LOAD">
    public static String getDataForLoadNext(Integer status) {
        String result = "SELECT data_for_load_id, reporter_id, period_id, commodity_group_type_id, count, status\n"
                + "FROM cls_data_for_load\n"
                + "WHERE status = @status;";
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestNext(String filename, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_NEXT);
        result = result.replace("@status", status.toString());
        result = result.replace("@filename", filename);
        return result;
    }

    public static String getLoadRequestNext(Integer status) {
        String result = "SELECT load_request_id, url, filename, load_request_start, load_request_end, status, count\n"
                + "FROM cls_load_request\n"
                + "WHERE status = @status;";
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getDataForLoadNext(Integer status, Integer reporterId, Integer commodityGroupTypeId, Integer maxCount, String notInIds) {
        String result = "SELECT data_for_load_id, reporter_id, period_id, commodity_group_type_id, count, status\n"
                + "FROM cls_data_for_load\n"
                + "WHERE status = @status "
                + "AND reporter_id = @reporter_id "
                + "AND commodity_group_type_id = @commoditygroup_type_id "
                + "AND count <= @max_count "
                + "AND data_for_load_id NOT IN (@not_in_ids);";
        result = result.replace("@status", status.toString());
        result = result.replace("@reporter_id", reporterId.toString());
        result = result.replace("@commoditygroup_type_id", commodityGroupTypeId.toString());
        result = result.replace("@max_count", maxCount.toString());
        result = result.replace("@not_in_ids", notInIds);
        return result;
    }

    public static String getDataForLoadGroup(Integer reporterId, Integer periodId, Integer commodityGroupTypeId, Integer status) {
        String result = "SELECT data_for_load_id, reporter_id, period_id, commodity_group_type_id, count, status\n"
                + "FROM cls_data_for_load\n"
                + "WHERE status = @status \n"
                + "	AND reporter_id IN (SELECT country_id FROM dim_country WHERE load_group_id IN (SELECT load_group_id FROM dim_country WHERE country_id = @reporter_id))\n"
                + "	AND period_id IN (SELECT period_id FROM cls_period_group WHERE group_id IN (SELECT group_id FROM cls_period_group WHERE period_id = @period_id AND commodity_group_type_id = @commodity_group_type_id) AND commodity_group_type_id = @commodity_group_type_id)\n"
                + "	AND commodity_group_type_id = @commodity_group_type_id\n"
                + ";";
        result = result.replace("@reporter_id", reporterId.toString());
        result = result.replace("@period_id", periodId.toString());
        result = result.replace("@status", status.toString());
        result = result.replace("@commodity_group_type_id", commodityGroupTypeId.toString());
        return result;
    }

    public static String getDataForLoadGroupIds(Integer reporterId, Integer periodId, Integer commodityGroupTypeId, Integer status) {
        String result = "SELECT GROUP_CONCAT(data_for_load_id)\n"
                + "FROM cls_data_for_load\n"
                + "WHERE status = @status \n"
                + "	AND reporter_id IN (SELECT country_id FROM dim_country WHERE load_group_id IN (SELECT load_group_id FROM dim_country WHERE country_id = @reporter_id))\n"
                + "	AND period_id IN (SELECT period_id FROM cls_period_group WHERE group_id IN (SELECT group_id FROM cls_period_group WHERE period_id = @period_id AND commodity_group_type_id = @commodity_group_type_id) AND commodity_group_type_id = @commodity_group_type_id)\n"
                + "	AND commodity_group_type_id = @commodity_group_type_id\n"
                + ";";
        result = result.replace("@reporter_id", reporterId.toString());
        result = result.replace("@period_id", periodId.toString());
        result = result.replace("@status", status.toString());
        result = result.replace("@commodity_group_type_id", commodityGroupTypeId.toString());
        return result;
    }

    public static String getDataForLoadGroupIdsUpdateStatus(String ids, Integer status) {
        String result = "UPDATE cls_data_for_load SET status = @status\n"
                + "WHERE data_for_load_id IN (@ids);COMMIT;";
        result = result.replace("@ids", ids);
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestUpdateStatusToExecution(Integer id, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_UPDATE_TO_EXECUTION);
        result = result.replace("@id", id.toString());
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestUpdateStatusToExecuted(Integer id, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_UPDATE_TO_EXECUTED);
        result = result.replace("@id", id.toString());
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestUpdateStatusToFailed(Integer id, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_UPDATE_TO_FAILED);
        result = result.replace("@id", id.toString());
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestUpdateStatusToReady(Integer id, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_UPDATE_TO_READY);
        result = result.replace("@id", id.toString());
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getLoadRequestUpdateCountAsIs(Integer id) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_UPDATE_COUNT);
        result = result.replace("@id", id.toString());
        return result;
    }

    public static String getInsertLocalLoadRequest(String fileName, Integer status) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_CLS_LOAD_REQUEST_INSERT);
        result = result.replace("@fileName", fileName);
        result = result.replace("@status", status.toString());
        return result;
    }

    public static String getDataForLoadGroupsToLoadRequest(String ids, Boolean groupByPartners) {
        if (groupByPartners) {
            String result = "INSERT INTO cls_load_request\n"
                    + "(url, filename)\n"
                    + "SELECT \n"
                    + "	CONCAT('http://comtrade.un.org/api/get?freq=A&rg=All&cc=All&fmt=csv&max=100000&type=C&head=H'\n"
                    + "	,'&r=', t1.reporter_ids\n"
                    + "	,'&ps=', t1.period_ids\n"
                    + "	,'&px=', t1.commodity_group_type_code\n"
                    + "	,'&p=', t1.partner_ids\n"
                    + "	) url,\n"
                    + "	CONCAT(t1.commodity_group_type_code, '-', t1.reporter_codes, '-', t1.period_codes, '-', t1.partner_codes,'.txt') filename\n"
                    + "FROM (\n"
                    + "SELECT GROUP_CONCAT(DISTINCT t1.period_id) period_ids\n"
                    + "	, GROUP_CONCAT(DISTINCT t1.period_id ORDER BY t1.period_id SEPARATOR '-') period_codes\n"
                    + "	, GROUP_CONCAT(DISTINCT t2.comtrade_id) reporter_ids\n"
                    + "	, GROUP_CONCAT(DISTINCT t2.comtrade_code ORDER BY t2.comtrade_code SEPARATOR '-') reporter_codes\n"
                    + "	, GROUP_CONCAT(DISTINCT t4.commodity_group_type_code) commodity_group_type_code\n"
                    + "	, t3.partner_ids\n"
                    + "	, t3.partner_codes\n"
                    + "FROM cls_data_for_load t1, dim_country t2\n"
                    + ", (\n"
                    + "SELECT GROUP_CONCAT(comtrade_id) partner_ids, GROUP_CONCAT(DISTINCT comtrade_code ORDER BY comtrade_code SEPARATOR '-') partner_codes\n"
                    + "FROM dim_country\n"
                    + "WHERE load_group_id IS NOT NULL\n"
                    + "GROUP BY load_group_id\n"
                    + "ORDER BY load_group_id) t3\n"
                    + ", cls_commodity_group_type t4\n"
                    + "WHERE t1.data_for_load_id IN (@ids)\n"
                    + "	AND t1.reporter_id = t2.country_id\n"
                    + "	AND t1.commodity_group_type_id = t4.commodity_group_type_id\n"
                    + "GROUP BY t3.partner_ids, t3.partner_codes) t1;\n"
                    + ";";
            result = result.replace("@ids", ids);
            return result;
        } else {
            String result = "INSERT INTO cls_load_request (url, filename)\n"
                    + "SELECT \n"
                    + "	CONCAT('http://comtrade.un.org/api/get?freq=A&rg=All&cc=All&fmt=csv&max=100000&type=C&head=H&p=All'\n"
                    + "	,'&r=', t1.reporter_ids\n"
                    + "	,'&ps=', t1.period_ids\n"
                    + "	,'&px=', t1.commodity_group_type_code) url,\n"
                    + "	CONCAT('', t1.commodity_group_type_code, '-', t1.reporter_codes, '-', t1.period_codes,'.txt') filename\n"
                    + "FROM (\n"
                    + "SELECT GROUP_CONCAT(DISTINCT t1.period_id) period_ids\n"
                    + "	, GROUP_CONCAT(DISTINCT t1.period_id ORDER BY t1.period_id SEPARATOR '-') period_codes\n"
                    + "	, GROUP_CONCAT(DISTINCT t2.comtrade_id) reporter_ids\n"
                    + "	, GROUP_CONCAT(DISTINCT t2.comtrade_code ORDER BY t2.comtrade_code SEPARATOR '-') reporter_codes\n"
                    + "	, GROUP_CONCAT(DISTINCT t4.commodity_group_type_code) commodity_group_type_code\n"
                    + "FROM cls_data_for_load t1, dim_country t2, cls_commodity_group_type t4\n"
                    + "WHERE t1.data_for_load_id IN (@ids)\n"
                    + "	AND t1.reporter_id = t2.country_id\n"
                    + "	AND t1.commodity_group_type_id = t4.commodity_group_type_id\n"
                    + ") t1;";
            result = result.replace("@ids", ids);
            return result;
        }
    }

    public static String getRankByPartnerDeleteFromFctTrade(Integer loadRequestId) {
        String result = "\n"
                + "SELECT CONCAT('DELETE FROM fct_trade WHERE period_id = ', period_id, ' AND reporter_id = ',reporter_id,' AND trade_flow_id = ',trade_flow_id,' AND commodity_group_type_id = ',commodity_group_type_id,';','')\n"
                + "FROM (SELECT DISTINCT period_id, trade_flow_id, reporter_id, commodity_group_type_id\n"
                + "	FROM inp_trade_data_id_@loadRequestId)d;"
                + "";
        result = result.replace("@loadRequestId", loadRequestId.toString());
        return result;
    }

    public static String getTradeLoadPreparation(Integer loadRequestId, String fileName) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_PREPARATION);
        result = result.replace("@load_request_id", loadRequestId.toString());
        result = result.replace("@file_name", fileName);
        return result;
    }

    public static String getTradeLoadExecution(Integer loadRequestId, String partitions, String deletions) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_EXECUTION);
        result = result.replace("@load_request_id", loadRequestId.toString());
        result = result.replace("@partitions", partitions);
        result = result.replace("@deletions", deletions);
        return result;
    }

    public static String getAlterPartitionsFctTrade(Integer loadRequestId) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_INP_TRADE_DATA_ID_SELECT_PARTITIONS);
        result = result.replace("@load_request_id", loadRequestId.toString());
        return result;
    }

    public static String getDistinctInpTradeDataTargetTable(Integer loadRequestId) {
        String result = SqlTemplates.getSql(SqlTemplates.TRADE_LOAD_INP_TRADE_DATA_ID_SELECT_DELETE_STMTS);
        result = result.replace("@load_request_id", loadRequestId.toString());
        return result;
    }

    // </editor-fold>
    public static String getChangeGroupCommodityUrlCodes(String codes, String group) {
        String result = "SELECT IFNULL(GROUP_CONCAT(target_url_code SEPARATOR '-'), CONCAT('@group', 'TOTAL'))\n"
                + "FROM cls_commodity_conversion\n"
                + "WHERE source_url_code IN (@codes) AND target_group_url_code = '@group'";
        result = result.replace("@group", group);
        result = result.replace("@codes", codes);
        return result;
    }

    public static String getTradeSunburstData(String reporterIds, String tradeFlowIds, String partnerIds, String periodIds, String commodityGroupTypeId, String commodityIds, String locale) {
        String result = gew.sql.SqlTemplates.getSql(gew.sql.SqlTemplates.TRADE_SUNBURST_GRAPH);
        result = result.replace("@reporterIds", reporterIds);
        result = result.replace("@tradeFlowIds", tradeFlowIds);
        result = result.replace("@partnerIds", partnerIds);
        result = result.replace("@periodIds", periodIds);
        result = result.replace("@commodityGroupTypeId", commodityGroupTypeId);
        result = result.replace("@commodityIds", commodityIds);
        result = result.replace("@name", ResourceBundle.getBundle(BUNDLE_NAME, LocaleService.getLocale(locale)).getString(LabelCode.SQL_HIER_NAME));
        return result;
    }

    public static String getTradeTopNPartnersData(Integer reporterId, Integer tradeFlowId, String periodIds, Integer commodityId, Integer tradeValuePartnerTotalRank, Integer partnerTotalId, Integer otherPartnersId) {
        String result = SqlTemplates.getSql(SqlTemplates.GRAPH_TRADE_TOPN_PARTNERS);
        result = result.replace("@period_ids", periodIds);
        result = result.replace("@other_partners_id", otherPartnersId.toString());
        result = result.replace("@reporter_id", reporterId.toString());
        result = result.replace("@commodity_id", commodityId.toString());
        result = result.replace("@trade_flow_id", tradeFlowId.toString());
        result = result.replace("@trade_value_partner_total_rank", tradeValuePartnerTotalRank.toString());
        result = result.replace("@partner_total_id", partnerTotalId.toString());
        return result;
    }

    public static String getTradeTopNPartners2Data(String locale, Integer reporterId, Integer tradeFlowId, String periodIds, Integer commodityId, Integer tradeValuePartnerTotalRank, Integer partnerTotalId, Integer otherPartnersId) {
        String result = SqlTemplates.getSql(SqlTemplates.GRAPH_TRADE_TOPN_PARTNERS2);
        result = result.replace("@locale", locale);
        result = result.replace("@period_ids", periodIds);
        result = result.replace("@other_partners_id", otherPartnersId.toString());
        result = result.replace("@reporter_id", reporterId.toString());
        result = result.replace("@commodity_id", commodityId.toString());
        result = result.replace("@trade_flow_id", tradeFlowId.toString());
        result = result.replace("@trade_value_partner_total_rank", tradeValuePartnerTotalRank.toString());
        result = result.replace("@partner_total_id", partnerTotalId.toString());
        return result;
    }

    public static String getIndicatorsGraphData(List<Integer> countryIds, String countryIdsStr, String indicatorIds) {
        String values = "";
        String valuesTemplate = SqlTemplates.getSql(SqlTemplates.GRAPH_INDICATORS_VALUE);
        for (int i = 0; i < countryIds.size(); i++) {
            values = values.concat(valuesTemplate.replace("@countryId@", countryIds.get(i).toString()));
        }
        String result = SqlTemplates.getSql(SqlTemplates.GRAPH_INDICATORS);
        result = result.replace("@values@", values);
        result = result.replace("@countryIds@", countryIdsStr);
        result = result.replace("@indicatorIds@", indicatorIds);
        return result;
    }

    public static String getIndicatorsTableValues(String countryIds, String indicatorIds) {
        String result = SqlTemplates.getSql(SqlTemplates.TABLE_INDICATORS_YEARS);
        result = result.replace("@countryIds@", countryIds);
        result = result.replace("@indicatorIds@", indicatorIds);
        return result;
    }

    public static String getIndicatorsTableData(String countryIdsStr, String indicatorIds, List<String> periods, String locale) {
        String orderBy = periods.isEmpty() ? SqlUtils.ONE
                : SqlTemplates.getSql(SqlTemplates.TABLE_INDICATORS_ORDER_BY).replace("@yearId@", periods.get(0));
        String valuesStr = "";
        String valueTemplate = SqlTemplates.getSql(SqlTemplates.TABLE_INDICATORS_VALUE_FORMAT).replace("@locale@", locale);
        for (int i = 0; i < periods.size(); i++) {
            valuesStr = valuesStr.concat(valueTemplate.replace("@yearId@", periods.get(i)));
        }
        String result = SqlTemplates.getSql(SqlTemplates.TABLE_INDICATORS);
        result = result.replace("@values@", String.join(SqlUtils.COMMA, valuesStr));
        result = result.replace("@countryIds@", countryIdsStr);
        result = result.replace("@indicatorIds@", indicatorIds);
        result = result.replace("@orderBy@", orderBy);
        return result;
    }

    public static String getPartnerUrlCollapse(String selectedIds, Integer id) {
        String result = SqlTemplates.getSql(SqlTemplates.HIER_PARTNER_URL_COLLAPSE);
        result = result.replace("@ids", selectedIds);
        result = result.replace("@id", id.toString());
        return result;
    }

    public static String getIndicatorsSummary(String locale) {
        String result = SqlTemplates.getSql(SqlTemplates.INDICATOR_SUMMARY);
        result = result.replace("@p_locale@", locale);
        return result;
    }

    public static String getIndicatorsSummaryIndicators(String locale) {
        String result = SqlTemplates.getSql(SqlTemplates.INDICATOR_SUMMARY_INDICATORS);
        result = result.replace("@locale@", locale);
        return result;
    }

    public static String getTree2Init(String locale, String treeTypeId, String selectedIds, String urlBegin, String urlVar, String urlEnd) {
        String result = SqlTemplates.getSql(SqlTemplates.TREE_INIT);
        result = result.replace("@locale@", locale);
        result = result.replace("@treeTypeId@", treeTypeId);
        result = result.replace("@selectedIds@", selectedIds);
        result = result.replace("@urlBegin@", urlBegin);
        result = result.replace("@urlVar@", urlVar);
        result = result.replace("@urlEnd@", urlEnd);
        return result;
    }

    public static String getTree2Children(String locale, String treeTypeId, String selectedIds, String urlBegin, String urlVar, String urlEnd) {
        String result = SqlTemplates.getSql(SqlTemplates.TREE_CHILDREN);
        result = result.replace("@locale@", locale);
        result = result.replace("@treeTypeId@", treeTypeId);
        result = result.replace("@selectedIds@", selectedIds);
        result = result.replace("@urlBegin@", urlBegin);
        result = result.replace("@urlVar@", urlVar);
        result = result.replace("@urlEnd@", urlEnd);
        return result;
    }

    public static String getTree2Search(String locale, String treeTypeId, String selectedIds, String urlBegin, String urlVar, String urlEnd, String filteredIds, String additionalCondition) {
        String result = SqlTemplates.getSql(SqlTemplates.TREE_SEARCH);
        result = result.replace("@additionalCondition@", additionalCondition);
        result = result.replace("@locale@", locale);
        result = result.replace("@treeTypeId@", treeTypeId);
        result = result.replace("@ids@", selectedIds);
        result = result.replace("@urlBegin@", urlBegin);
        result = result.replace("@urlVar@", urlVar);
        result = result.replace("@urlEnd@", urlEnd);
        result = result.replace("@filteredIds@", filteredIds);
        return result;
    }

    public static String getTree2SearchIds(int treeTypeId, String searchText) {
        String treeTypeIdStr = (new Integer(treeTypeId)).toString();
        return getTree2SearchIds(treeTypeIdStr, searchText);
    }

    public static String getTree2SearchIds(String treeTypeId, String searchText) {
        String result = SqlTemplates.getSql(SqlTemplates.TREE_SEARCH_IDS);
        result = result.replace("@treeTypeId@", treeTypeId);
        result = result.replace("@filterWords@", searchText);
        result = result.replace("@upperWordsCs@", searchText.trim().toUpperCase().replace(SqlUtils.SPACE, SqlUtils.QUOTE.concat(SqlUtils.COMMA).concat(SqlUtils.QUOTE)));
        return result;
    }

    public static String getTableDimension(String locale, String ids, String hierGroupId, String linkTypes, String selectables, boolean noParents) {
        String result = SqlTemplates.getSql(SqlTemplates.TABLE_DIMENSION);
        result = result.replace("@ids@", ids);
        result = result.replace("@hierGroupId@", hierGroupId);
        result = result.replace("@linkTypes@", linkTypes);
        result = result.replace("@locale@", locale);
        result = result.replace("@selectables@", selectables);
        String noParentsStr = noParents
                ? SqlTemplates.getSql(SqlTemplates.TABLE_DIMENSION_CONDITION_NO_PARENTS)
                : "";
        result = result.replace("@noParents@", noParentsStr);
        return result;
    }

    public static String getLogRequest(final String section,
            final String sessionId, final Long sessionCreationTime,
            final Long sessionLastAccessedTime, final boolean sessionIsNew,
            final String characterEncoding, final String localAddr,
            final String localName, final Integer localPort,
            final Locale locale, final String protocol,
            final String remoteAddr, final String remoteHost,
            final Integer remotePort, final String remoteUser,
            final String requestUri, final String requestedSessionId,
            final String scheme, final String serverName,
            final Integer serverPort, final String servletPath) {
        String result = SqlTemplates.getSql(SqlTemplates.LOG_REQUEST);
        result = result.replace("@section@", section == null ? SqlUtils.EMPTY : section);
        result = result.replace("@session_id@", sessionId == null ? SqlUtils.EMPTY : sessionId);
        result = result.replace("@session_creation_time@", sessionCreationTime == null ? SqlUtils.SQL_NULL : sessionCreationTime.toString());
        result = result.replace("@session_last_accessed_time@", sessionLastAccessedTime == null ? SqlUtils.SQL_NULL : sessionLastAccessedTime.toString());
        result = result.replace("@session_is_new@", sessionIsNew ? SqlUtils.ONE : SqlUtils.ZERO);
        result = result.replace("@character_encoding@", characterEncoding == null ? SqlUtils.EMPTY : characterEncoding);
        result = result.replace("@local_addr@", localAddr == null ? SqlUtils.EMPTY : localAddr);
        result = result.replace("@local_name@", localName == null ? SqlUtils.EMPTY : localName);
        result = result.replace("@local_port@", localPort == null ? SqlUtils.SQL_NULL : localPort.toString());
        result = result.replace("@locale_region@", locale == null ? SqlUtils.EMPTY : locale.getCountry());
        result = result.replace("@locale_language@", locale == null ? SqlUtils.EMPTY : locale.getLanguage());
        result = result.replace("@protocol@", protocol == null ? SqlUtils.EMPTY : protocol);
        result = result.replace("@remote_addr@", remoteAddr == null ? SqlUtils.EMPTY : remoteAddr);
        result = result.replace("@remote_host@", remoteHost == null ? SqlUtils.EMPTY : remoteHost);
        result = result.replace("@remote_port@", remotePort == null ? SqlUtils.SQL_NULL : remotePort.toString());
        result = result.replace("@remote_user@", remoteUser == null ? SqlUtils.EMPTY : remoteUser);
        result = result.replace("@request_uri@", requestUri == null ? SqlUtils.EMPTY : requestUri);
        result = result.replace("@requested_session_id@", requestedSessionId == null ? SqlUtils.EMPTY : requestedSessionId);
        result = result.replace("@scheme@", scheme == null ? SqlUtils.EMPTY : scheme);
        result = result.replace("@server_name@", serverName == null ? SqlUtils.EMPTY : serverName);
        result = result.replace("@server_port@", serverPort == null ? SqlUtils.SQL_NULL : serverPort.toString());
        result = result.replace("@servlet_path@", servletPath == null ? SqlUtils.EMPTY : servletPath);
        return result;
    }
    
    public static String getLogCookie(final String sessionId, final String name, final String value){
        String result = SqlTemplates.getSql(SqlTemplates.LOG_COOKIE);        
        result = result.replace("@session_id@", sessionId == null ? SqlUtils.EMPTY : sessionId);
        result = result.replace("@name@", name == null ? SqlUtils.EMPTY : name);
        result = result.replace("@value@", value == null ? SqlUtils.EMPTY : value);
        return result;
    }
    public static String getLogHeader(final String sessionId, final String name, final String value){
        String result = SqlTemplates.getSql(SqlTemplates.LOG_HEADER);        
        result = result.replace("@session_id@", sessionId == null ? SqlUtils.EMPTY : sessionId);
        result = result.replace("@name@", name == null ? SqlUtils.EMPTY : name);
        result = result.replace("@value@", value == null ? SqlUtils.EMPTY : value);
        return result;
    }
}
